const mysql = require('mysql2/promise');
require('dotenv').config({ path: './config.env' });

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'zhangzheyi050608',
  port: process.env.DB_PORT || 3306,
  multipleStatements: true
};

async function setupCompleteDatabase() {
  let connection;
  
  try {
    console.log('🔄 开始完整数据库设置...');
    
    // 连接MySQL
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 连接到MySQL服务器成功');

    // 使用数据库
    const dbName = process.env.DB_NAME || 'zhangzheyi';
    await connection.query(`USE ${dbName}`);
    console.log(`✅ 使用数据库: ${dbName}`);

    // 按依赖顺序删除所有相关表（先删子表再删主表）
    console.log('🔄 清理旧表结构...');
    await connection.execute('DROP TABLE IF EXISTS fund_nav');
    await connection.execute('DROP TABLE IF EXISTS fund_holdings');
    await connection.execute('DROP TABLE IF EXISTS fund_tag_relations');
    await connection.execute('DROP TABLE IF EXISTS portfolio_funds');
    await connection.execute('DROP TABLE IF EXISTS user_portfolios');
    await connection.execute('DROP TABLE IF EXISTS trade_order_details');
    await connection.execute('DROP TABLE IF EXISTS settlements');
    await connection.execute('DROP TABLE IF EXISTS trade_orders');
    await connection.execute('DROP TABLE IF EXISTS audit_logs');
    await connection.execute('DROP TABLE IF EXISTS portfolio_performance');
    await connection.execute('DROP TABLE IF EXISTS portfolios');
    await connection.execute('DROP TABLE IF EXISTS strategy_backtest');
    await connection.execute('DROP TABLE IF EXISTS strategy_funds');
    await connection.execute('DROP TABLE IF EXISTS strategies');
    await connection.execute('DROP TABLE IF EXISTS funds');
    await connection.execute('DROP TABLE IF EXISTS managers');
    await connection.execute('DROP TABLE IF EXISTS companies');
    await connection.execute('DROP TABLE IF EXISTS risk_levels');
    await connection.execute('DROP TABLE IF EXISTS fund_categories');
    await connection.execute('DROP TABLE IF EXISTS fund_types');
    await connection.execute('DROP TABLE IF EXISTS fund_tags');
    await connection.execute('DROP TABLE IF EXISTS factor_relations');
    await connection.execute('DROP TABLE IF EXISTS factors');
    await connection.execute('DROP TABLE IF EXISTS users');

    // 1. 创建基金类型表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_types (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) UNIQUE NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 2. 创建基金分类表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_categories (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) UNIQUE NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 3. 创建风险等级表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS risk_levels (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(20) UNIQUE NOT NULL,
        description TEXT,
        color VARCHAR(20),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 4. 创建基金标签表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_tags (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) UNIQUE NOT NULL,
        category VARCHAR(50),
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 5. 创建基金公司表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS companies (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        code VARCHAR(20) UNIQUE NOT NULL,
        establishment_date DATE,
        total_assets DECIMAL(20,2) DEFAULT 0,
        fund_count INT DEFAULT 0,
        description TEXT,
        website VARCHAR(200),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 6. 创建基金经理表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS managers (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        company_id INT,
        company_name VARCHAR(100),
        experience_years INT DEFAULT 0,
        fund_count INT DEFAULT 0,
        avg_return DECIMAL(5,2) DEFAULT 0,
        education VARCHAR(200),
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 7. 创建基金表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS funds (
        id INT PRIMARY KEY AUTO_INCREMENT,
        code VARCHAR(20) UNIQUE NOT NULL,
        name VARCHAR(200) NOT NULL,
        company_id INT,
        manager_id INT,
        company_name VARCHAR(100),
        manager_name VARCHAR(100),
        fund_type_id INT,
        fund_type VARCHAR(50),
        category_id INT,
        category VARCHAR(50),
        risk_level_id INT,
        risk_level ENUM('低风险', '中低风险', '中风险', '中高风险', '高风险'),
        tags TEXT,
        nav DECIMAL(10,4),
        nav_date DATE,
        total_assets DECIMAL(20,2),
        establishment_date DATE,
        status ENUM('active', 'inactive') DEFAULT 'active',
        daily_return DECIMAL(5,2),
        weekly_return DECIMAL(5,2),
        monthly_return DECIMAL(5,2),
        yearly_return DECIMAL(5,2),
        max_drawdown DECIMAL(5,2),
        sharpe_ratio DECIMAL(5,2),
        benchmark VARCHAR(100),
        management_fee DECIMAL(5,2),
        custodian_fee DECIMAL(5,2),
        subscription_fee DECIMAL(5,2),
        redemption_fee DECIMAL(5,2),
        min_investment DECIMAL(10,2),
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id),
        FOREIGN KEY (manager_id) REFERENCES managers(id),
        FOREIGN KEY (fund_type_id) REFERENCES fund_types(id),
        FOREIGN KEY (category_id) REFERENCES fund_categories(id),
        FOREIGN KEY (risk_level_id) REFERENCES risk_levels(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 8. 创建基金净值表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_nav (
        id INT PRIMARY KEY AUTO_INCREMENT,
        fund_id INT NOT NULL,
        nav_date DATE NOT NULL,
        nav DECIMAL(10,4) NOT NULL,
        cumulative_nav DECIMAL(10,4),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id),
        UNIQUE KEY unique_fund_date (fund_id, nav_date)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 9. 创建基金持仓表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_holdings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        fund_id INT NOT NULL,
        stock_code VARCHAR(20),
        stock_name VARCHAR(100),
        weight DECIMAL(5,2),
        shares BIGINT,
        market_value DECIMAL(20,2),
        report_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 10. 创建基金标签关联表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_tag_relations (
        id INT PRIMARY KEY AUTO_INCREMENT,
        fund_id INT NOT NULL,
        tag_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
        FOREIGN KEY (tag_id) REFERENCES fund_tags(id) ON DELETE CASCADE,
        UNIQUE KEY unique_fund_tag (fund_id, tag_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 11. 创建用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        name VARCHAR(100) NOT NULL,
        role ENUM('admin', 'manager', 'user') DEFAULT 'user',
        status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_login TIMESTAMP NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 12. 创建用户组合表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS user_portfolios (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        tags JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 13. 创建组合基金关联表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS portfolio_funds (
        id INT PRIMARY KEY AUTO_INCREMENT,
        portfolio_id INT NOT NULL,
        fund_id INT NOT NULL,
        weight DECIMAL(5,2) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (portfolio_id) REFERENCES user_portfolios(id) ON DELETE CASCADE,
        FOREIGN KEY (fund_id) REFERENCES funds(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    console.log('✅ 所有数据表创建成功');

    // 插入基础数据
    await insertBaseData(connection);
    console.log('✅ 基础数据插入成功');

    // 插入基金数据
    await insertFundData(connection);
    console.log('✅ 基金数据插入成功');

    console.log('🎉 完整数据库设置完成！');

  } catch (error) {
    console.error('❌ 数据库设置失败:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

async function insertBaseData(connection) {
  // 插入基金类型
  const fundTypes = [
    { name: '股票型', description: '主要投资于股票市场的基金' },
    { name: '混合型', description: '同时投资股票和债券的基金' },
    { name: '债券型', description: '主要投资于债券市场的基金' },
    { name: '指数型', description: '跟踪特定指数的基金' },
    { name: '货币型', description: '投资于货币市场工具的基金' },
    { name: 'QDII', description: '合格境内机构投资者基金' }
  ];

  for (const type of fundTypes) {
    await connection.execute(
      'INSERT IGNORE INTO fund_types (name, description) VALUES (?, ?)',
      [type.name, type.description]
    );
  }

  // 插入基金分类
  const fundCategories = [
    { name: '成长型', description: '追求资本增值的基金' },
    { name: '价值型', description: '投资被低估股票的基金' },
    { name: '平衡型', description: '平衡收益和风险的基金' },
    { name: '行业主题', description: '专注于特定行业的基金' },
    { name: '个股', description: '投资单一股票的基金' },
    { name: '行业指数', description: '跟踪行业指数的基金' },
    { name: '纯债', description: '只投资于债券的基金' },
    { name: '货币市场', description: '投资于货币市场工具的基金' }
  ];

  for (const category of fundCategories) {
    await connection.execute(
      'INSERT IGNORE INTO fund_categories (name, description) VALUES (?, ?)',
      [category.name, category.description]
    );
  }

  // 插入风险等级
  const riskLevels = [
    { name: '低风险', description: '适合保守型投资者', color: 'green' },
    { name: '中低风险', description: '适合稳健型投资者', color: 'blue' },
    { name: '中风险', description: '适合平衡型投资者', color: 'orange' },
    { name: '中高风险', description: '适合成长型投资者', color: 'red' },
    { name: '高风险', description: '适合激进型投资者', color: 'volcano' }
  ];

  for (const level of riskLevels) {
    await connection.execute(
      'INSERT IGNORE INTO risk_levels (name, description, color) VALUES (?, ?, ?)',
      [level.name, level.description, level.color]
    );
  }

  // 插入基金标签
  const fundTags = [
    { name: '成长型', category: '投资风格' },
    { name: '价值投资', category: '投资风格' },
    { name: '消费行业', category: '行业主题' },
    { name: '科技股', category: '行业主题' },
    { name: '医药', category: '行业主题' },
    { name: '白酒', category: '行业主题' },
    { name: '混合型', category: '基金类型' },
    { name: '股票型', category: '基金类型' },
    { name: '指数型', category: '基金类型' },
    { name: '主题投资', category: '投资策略' },
    { name: '被动投资', category: '投资策略' },
    { name: '个股', category: '投资标的' },
    { name: '行业指数', category: '投资标的' },
    { name: '创新药', category: '细分主题' },
    { name: '生物科技', category: '细分主题' },
    { name: '稳健', category: '风险特征' },
    { name: '分红', category: '收益特征' }
  ];

  for (const tag of fundTags) {
    await connection.execute(
      'INSERT IGNORE INTO fund_tags (name, category, description) VALUES (?, ?, ?)',
      [tag.name, tag.category, `标签: ${tag.name}`]
    );
  }

  // 插入基金公司
  const companies = [
    { name: '华夏基金管理有限公司', code: 'HX', establishment_date: '1998-04-09', total_assets: 1000000000000.00, fund_count: 150, description: '华夏基金是中国首批成立的基金管理公司之一' },
    { name: '易方达基金管理有限公司', code: 'YFD', establishment_date: '2001-04-17', total_assets: 800000000000.00, fund_count: 120, description: '易方达基金是国内领先的综合性资产管理公司' },
    { name: '嘉实基金管理有限公司', code: 'JS', establishment_date: '1999-03-25', total_assets: 600000000000.00, fund_count: 100, description: '嘉实基金是中国最早成立的基金管理公司之一' },
    { name: '招商基金管理有限公司', code: 'ZS', establishment_date: '2002-12-27', total_assets: 400000000000.00, fund_count: 80, description: '招商基金是招商银行旗下的专业基金管理公司' },
    { name: '中欧基金管理有限公司', code: 'ZO', establishment_date: '2006-07-19', total_assets: 300000000000.00, fund_count: 60, description: '中欧基金是中外合资的基金管理公司' },
    { name: '汇添富基金管理有限公司', code: 'HTF', establishment_date: '2005-02-03', total_assets: 500000000000.00, fund_count: 90, description: '汇添富基金是国内知名的基金管理公司' },
    { name: '南方基金管理有限公司', code: 'NF', establishment_date: '1998-03-06', total_assets: 750000000000.00, fund_count: 110, description: '南方基金是中国首批规范的基金管理公司之一' },
    { name: '广发基金管理有限公司', code: 'GF', establishment_date: '2003-08-05', total_assets: 650000000000.00, fund_count: 95, description: '广发基金是业内领先的综合性资产管理机构' }
  ];

  for (const company of companies) {
    await connection.execute(
      'INSERT IGNORE INTO companies (name, code, establishment_date, total_assets, fund_count, description) VALUES (?, ?, ?, ?, ?, ?)',
      [company.name, company.code, company.establishment_date, company.total_assets, company.fund_count, company.description]
    );
  }

  const [companyRows] = await connection.execute('SELECT id, name FROM companies');
  const companyMap = companyRows.reduce((map, comp) => { map[comp.name] = comp.id; return map; }, {});

  // 插入基金经理
  const managers = [
    { name: '张坤', company_name: '易方达基金管理有限公司', experience_years: 15, fund_count: 8, avg_return: 18.5, education: '清华大学', description: '知名基金经理，擅长价值投资' },
    { name: '萧楠', company_name: '易方达基金管理有限公司', experience_years: 12, fund_count: 6, avg_return: 16.8, education: '北京大学', description: '消费行业投资专家' },
    { name: '刘彦春', company_name: '嘉实基金管理有限公司', experience_years: 18, fund_count: 10, avg_return: 20.2, education: '中国人民大学', description: '成长股投资专家' },
    { name: '侯昊', company_name: '招商基金管理有限公司', experience_years: 10, fund_count: 4, avg_return: 12.5, education: '复旦大学', description: '指数投资专家' },
    { name: '葛兰', company_name: '中欧基金管理有限公司', experience_years: 8, fund_count: 5, avg_return: 25.6, education: '北京大学', description: '医药行业投资专家' },
    { name: '刘江', company_name: '汇添富基金管理有限公司', experience_years: 11, fund_count: 7, avg_return: 19.3, education: '上海财经大学', description: '创新医药投资专家' },
    { name: '蔡向阳', company_name: '华夏基金管理有限公司', experience_years: 16, fund_count: 9, avg_return: 14.7, education: '清华大学', description: '稳健投资专家' },
    { name: '郑晓曦', company_name: '南方基金管理有限公司', experience_years: 9, fund_count: 5, avg_return: 15.1, education: '浙江大学', description: '科技股猎手，专注于TMT领域' },
    { name: '傅友兴', company_name: '广发基金管理有限公司', experience_years: 14, fund_count: 6, avg_return: 17.5, education: '厦门大学', description: '价值与成长结合的投资风格' }
  ];

  for (const manager of managers) {
    const companyId = companyMap[manager.company_name];
    if (!companyId) {
        console.warn(`警告: 未找到基金经理 "${manager.name}" 的公司 "${manager.company_name}"。将忽略此基金经理。`);
        continue;
    }
    await connection.execute(
      'INSERT IGNORE INTO managers (name, company_id, company_name, experience_years, fund_count, avg_return, education, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [manager.name, companyId, manager.company_name, manager.experience_years, manager.fund_count, manager.avg_return, manager.education, manager.description]
    );
  }

  // 插入用户
  const bcrypt = require('bcryptjs');
  const hashedPassword = await bcrypt.hash('admin', 12);
  const testPassword = await bcrypt.hash('123456', 12);
  
  await connection.execute(`
    INSERT IGNORE INTO users (username, password, email, name, role, status) 
    VALUES ('admin', ?, 'admin@example.com', '系统管理员', 'admin', 'active')
  `, [hashedPassword]);

  await connection.execute(`
    INSERT IGNORE INTO users (username, password, email, name, role, status) 
    VALUES ('test', ?, 'test@example.com', '测试用户', 'user', 'active')
  `, [testPassword]);
}

async function insertFundData(connection) {
  // 获取基础数据ID
  const [fundTypes] = await connection.execute('SELECT id, name FROM fund_types');
  const [fundCategories] = await connection.execute('SELECT id, name FROM fund_categories');
  const [riskLevels] = await connection.execute('SELECT id, name FROM risk_levels');
  const [companies] = await connection.execute('SELECT id, name FROM companies');
  const [managers] = await connection.execute('SELECT id, name, company_id FROM managers');

  // 创建映射
  const typeMap = fundTypes.reduce((map, type) => { map[type.name] = type.id; return map; }, {});
  const categoryMap = fundCategories.reduce((map, cat) => { map[cat.name] = cat.id; return map; }, {});
  const riskMap = riskLevels.reduce((map, risk) => { map[risk.name] = risk.id; return map; }, {});
  const companyMap = companies.reduce((map, comp) => { map[comp.name] = comp.id; return map; }, {});
  const managerMap = managers.reduce((map, mgr) => { map[mgr.name] = mgr.id; return map; }, {});

  // 基金数据
  const funds = [
    {
      code: '000001',
      name: '华夏成长混合',
      company_name: '华夏基金管理有限公司',
      manager_name: '蔡向阳',
      fund_type: '混合型',
      category: '成长型',
      risk_level: '中高风险',
      tags: ['成长型', '混合型', '消费行业', '价值投资'],
      nav: 2.856,
      total_assets: 125.6,
      establishment_date: '2001-12-18',
      daily_return: 1.25,
      weekly_return: 3.45,
      monthly_return: 8.92,
      yearly_return: 15.67,
      max_drawdown: -12.34,
      sharpe_ratio: 1.23,
      benchmark: '沪深300指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.2,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '专注于成长型股票投资，追求长期资本增值'
    },
    {
      code: '110022',
      name: '易方达消费行业股票',
      company_name: '易方达基金管理有限公司',
      manager_name: '萧楠',
      fund_type: '股票型',
      category: '行业主题',
      risk_level: '高风险',
      tags: ['消费行业', '股票型', '主题投资', '白酒'],
      nav: 3.245,
      total_assets: 98.3,
      establishment_date: '2010-08-20',
      daily_return: -0.85,
      weekly_return: 2.18,
      monthly_return: 6.34,
      yearly_return: 18.92,
      max_drawdown: -15.67,
      sharpe_ratio: 1.45,
      benchmark: '中证消费指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.5,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '专注于消费行业投资，重点配置白酒、家电等消费龙头'
    },
    {
      code: '000002',
      name: '嘉实增长混合',
      company_name: '嘉实基金管理有限公司',
      manager_name: '刘彦春',
      fund_type: '混合型',
      category: '成长型',
      risk_level: '中高风险',
      tags: ['成长型', '混合型', '科技股', '医药'],
      nav: 4.123,
      total_assets: 156.8,
      establishment_date: '2003-07-09',
      daily_return: 0.92,
      weekly_return: 4.67,
      monthly_return: 12.45,
      yearly_return: 22.34,
      max_drawdown: -18.92,
      sharpe_ratio: 1.67,
      benchmark: '中证800指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.2,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '追求长期增长，重点投资成长性良好的优质企业'
    },
    {
      code: '161725',
      name: '招商中证白酒指数',
      company_name: '招商基金管理有限公司',
      manager_name: '侯昊',
      fund_type: '指数型',
      category: '行业指数',
      risk_level: '高风险',
      tags: ['白酒', '指数型', '行业主题', '被动投资'],
      nav: 1.234,
      total_assets: 89.2,
      establishment_date: '2015-05-27',
      daily_return: -1.23,
      weekly_return: -2.45,
      monthly_return: -5.67,
      yearly_return: -8.92,
      max_drawdown: -25.34,
      sharpe_ratio: 0.89,
      benchmark: '中证白酒指数',
      management_fee: 0.6,
      custodian_fee: 0.1,
      subscription_fee: 0.8,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '跟踪中证白酒指数，投资白酒行业龙头企业'
    },
    {
      code: '000858',
      name: '易方达蓝筹精选混合',
      company_name: '易方达基金管理有限公司',
      manager_name: '张坤',
      fund_type: '混合型',
      category: '价值型',
      risk_level: '高风险',
      tags: ['价值投资', '混合型', '白酒', '大盘股'],
      nav: 2.158,
      total_assets: 645.6,
      establishment_date: '2018-09-05',
      daily_return: 0.55,
      weekly_return: 1.88,
      monthly_return: 5.72,
      yearly_return: 10.33,
      max_drawdown: -20.50,
      sharpe_ratio: 1.10,
      benchmark: '沪深300指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.5,
      redemption_fee: 0.5,
      min_investment: 10,
      description: '精选大盘蓝筹股，分享龙头企业成长价值'
    },
    {
      code: '000963',
      name: '中欧医疗健康混合',
      company_name: '中欧基金管理有限公司',
      manager_name: '葛兰',
      fund_type: '混合型',
      category: '行业主题',
      risk_level: '中高风险',
      tags: ['医疗健康', '混合型', '医药', '创新药'],
      nav: 2.456,
      total_assets: 67.8,
      establishment_date: '2016-09-29',
      daily_return: 1.67,
      weekly_return: 5.23,
      monthly_return: 14.56,
      yearly_return: 32.78,
      max_drawdown: -16.78,
      sharpe_ratio: 1.78,
      benchmark: '中证医药指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.2,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '专注于医疗健康行业投资，重点配置创新药、医疗器械等'
    },
    {
      code: '001766',
      name: '汇添富创新医药混合',
      company_name: '汇添富基金管理有限公司',
      manager_name: '刘江',
      fund_type: '混合型',
      category: '行业主题',
      risk_level: '中高风险',
      tags: ['创新医药', '混合型', '医药', '生物科技'],
      nav: 1.987,
      total_assets: 34.5,
      establishment_date: '2016-09-29',
      daily_return: 0.89,
      weekly_return: 3.45,
      monthly_return: 9.23,
      yearly_return: 25.67,
      max_drawdown: -14.56,
      sharpe_ratio: 1.56,
      benchmark: '中证医药指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.2,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '投资创新医药企业，分享医药行业创新发展红利'
    },
    {
      code: '002001',
      name: '华夏回报混合',
      company_name: '华夏基金管理有限公司',
      manager_name: '蔡向阳',
      fund_type: '混合型',
      category: '平衡型',
      risk_level: '中风险',
      tags: ['平衡型', '混合型', '稳健', '分红'],
      nav: 3.456,
      total_assets: 78.9,
      establishment_date: '2003-09-05',
      daily_return: 0.45,
      weekly_return: 2.34,
      monthly_return: 6.78,
      yearly_return: 12.45,
      max_drawdown: -8.92,
      sharpe_ratio: 1.34,
      benchmark: '沪深300指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.2,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '追求稳健收益，注重风险控制，适合稳健型投资者'
    },
    {
      code: '005827',
      name: '易方达上证50指数A',
      company_name: '易方达基金管理有限公司',
      manager_name: '张坤',
      fund_type: '指数型',
      category: '行业指数',
      risk_level: '中风险',
      tags: ['指数型', '上证50', '被动投资'],
      nav: 1.432,
      total_assets: 250.7,
      establishment_date: '2018-03-22',
      daily_return: 0.21,
      weekly_return: 1.10,
      monthly_return: 3.20,
      yearly_return: 8.50,
      max_drawdown: -10.15,
      sharpe_ratio: 0.95,
      benchmark: '上证50指数',
      management_fee: 0.5,
      custodian_fee: 0.1,
      subscription_fee: 0,
      redemption_fee: 0.5,
      min_investment: 1,
      description: '紧密跟踪上证50指数，投资于中国最具代表性的大盘蓝筹股'
    },
    {
      code: '519677',
      name: '交银新成长混合',
      company_name: '嘉实基金管理有限公司',
      manager_name: '刘彦春',
      fund_type: '混合型',
      category: '成长型',
      risk_level: '中高风险',
      tags: ['成长型', '科技股', '新兴产业'],
      nav: 2.891,
      total_assets: 55.4,
      establishment_date: '2011-10-26',
      daily_return: 1.15,
      weekly_return: 4.80,
      monthly_return: 11.55,
      yearly_return: 24.80,
      max_drawdown: -19.20,
      sharpe_ratio: 1.58,
      benchmark: '中证800成长指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.5,
      redemption_fee: 0.5,
      min_investment: 10,
      description: '发掘并投资于具备高成长潜力的新兴产业公司'
    },
    {
      code: '001508',
      name: '南方中证500ETF联接A',
      company_name: '南方基金管理有限公司',
      manager_name: '郑晓曦',
      fund_type: '指数型',
      category: '行业指数',
      risk_level: '中风险',
      tags: ['指数型', '中证500', '被动投资'],
      nav: 1.678,
      total_assets: 180.3,
      establishment_date: '2015-07-01',
      daily_return: 0.35,
      weekly_return: 1.55,
      monthly_return: 4.10,
      yearly_return: 9.80,
      max_drawdown: -13.45,
      sharpe_ratio: 1.05,
      benchmark: '中证500指数',
      management_fee: 0.5,
      custodian_fee: 0.1,
      subscription_fee: 0.1,
      redemption_fee: 0.5,
      min_investment: 10,
      description: '跟踪中证500指数，投资于中等市值、具有高成长性的公司'
    },
    {
      code: '000171',
      name: '广发稳健增长混合A',
      company_name: '广发基金管理有限公司',
      manager_name: '傅友兴',
      fund_type: '混合型',
      category: '平衡型',
      risk_level: '中低风险',
      tags: ['平衡型', '稳健', '价值投资'],
      nav: 2.105,
      total_assets: 95.1,
      establishment_date: '2004-07-26',
      daily_return: 0.15,
      weekly_return: 0.95,
      monthly_return: 2.85,
      yearly_return: 7.60,
      max_drawdown: -7.50,
      sharpe_ratio: 1.25,
      benchmark: '业绩比较基准',
      management_fee: 1.2,
      custodian_fee: 0.2,
      subscription_fee: 1.2,
      redemption_fee: 0.5,
      min_investment: 100,
      description: '以稳健增长为目标，在控制风险的前提下追求资产的长期增值'
    },
    {
      code: '217005',
      name: '招商安泰债券A',
      company_name: '招商基金管理有限公司',
      manager_name: '侯昊',
      fund_type: '债券型',
      category: '纯债',
      risk_level: '低风险',
      tags: ['债券型', '纯债', '稳健'],
      nav: 1.355,
      total_assets: 310.2,
      establishment_date: '2003-10-29',
      daily_return: 0.02,
      weekly_return: 0.10,
      monthly_return: 0.45,
      yearly_return: 3.80,
      max_drawdown: -2.10,
      sharpe_ratio: 2.50,
      benchmark: '中证全债指数',
      management_fee: 0.7,
      custodian_fee: 0.2,
      subscription_fee: 0.8,
      redemption_fee: 0,
      min_investment: 100,
      description: '主要投资于国债、金融债等固定收益类金融工具，追求稳定的票息收入'
    },
    {
      code: '000696',
      name: '中欧价值发现混合A',
      company_name: '中欧基金管理有限公司',
      manager_name: '葛兰',
      fund_type: '混合型',
      category: '价值型',
      risk_level: '中高风险',
      tags: ['价值投资', '大盘股'],
      nav: 2.489,
      total_assets: 88.8,
      establishment_date: '2009-07-22',
      daily_return: 0.78,
      weekly_return: 3.12,
      monthly_return: 7.95,
      yearly_return: 18.25,
      max_drawdown: -17.80,
      sharpe_ratio: 1.48,
      benchmark: '沪深300价值指数',
      management_fee: 1.5,
      custodian_fee: 0.25,
      subscription_fee: 1.5,
      redemption_fee: 0.5,
      min_investment: 1000,
      description: '深入挖掘并投资于价值被市场低估的上市公司'
    },
    {
      code: '000198',
      name: '华夏沪深300ETF联接A',
      company_name: '华夏基金管理有限公司',
      manager_name: '蔡向阳',
      fund_type: '指数型',
      category: '行业指数',
      risk_level: '中风险',
      tags: ['指数型', '沪深300', '被动投资'],
      nav: 1.521,
      total_assets: 512.3,
      establishment_date: '2009-07-07',
      daily_return: 0.30,
      weekly_return: 1.40,
      monthly_return: 3.80,
      yearly_return: 9.10,
      max_drawdown: -12.80,
      sharpe_ratio: 1.15,
      benchmark: '沪深300指数',
      management_fee: 0.5,
      custodian_fee: 0.1,
      subscription_fee: 0,
      redemption_fee: 0.5,
      min_investment: 1,
      description: '紧密跟踪沪深300指数，提供市场平均收益'
    },
    {
      code: '000181',
      name: '华夏货币A',
      company_name: '华夏基金管理有限公司',
      manager_name: '蔡向阳',
      fund_type: '货币型',
      category: '货币市场',
      risk_level: '低风险',
      tags: ['货币型', '稳健', '流动性好'],
      nav: 1.000,
      total_assets: 2300.5,
      establishment_date: '2004-04-07',
      daily_return: 0.005,
      weekly_return: 0.035,
      monthly_return: 0.15,
      yearly_return: 1.85,
      max_drawdown: 0,
      sharpe_ratio: 3.5,
      benchmark: '活期存款利率',
      management_fee: 0.33,
      custodian_fee: 0.1,
      subscription_fee: 0,
      redemption_fee: 0,
      min_investment: 1,
      description: '投资于高流动性的货币市场工具，风险低，是良好的现金管理工具'
    }
  ];

  // 插入基金数据
  for (const fund of funds) {
    const companyId = companyMap[fund.company_name];
    const managerId = managerMap[fund.manager_name];
    const typeId = typeMap[fund.fund_type];
    const categoryId = categoryMap[fund.category];
    const riskId = riskMap[fund.risk_level];

    const result = await connection.execute(`
      INSERT INTO funds (
        code, name, company_id, manager_id, company_name, manager_name,
        fund_type_id, fund_type, category_id, category, risk_level_id, risk_level,
        tags, nav, nav_date, total_assets, establishment_date, status,
        daily_return, weekly_return, monthly_return, yearly_return,
        max_drawdown, sharpe_ratio, benchmark,
        management_fee, custodian_fee, subscription_fee, redemption_fee,
        min_investment, description
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURDATE(), ?, ?, 'active', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      fund.code, fund.name, companyId, managerId, fund.company_name, fund.manager_name,
      typeId, fund.fund_type, categoryId, fund.category, riskId, fund.risk_level,
      JSON.stringify(fund.tags), fund.nav, fund.total_assets, fund.establishment_date,
      fund.daily_return, fund.weekly_return, fund.monthly_return, fund.yearly_return,
      fund.max_drawdown, fund.sharpe_ratio, fund.benchmark,
      fund.management_fee, fund.custodian_fee, fund.subscription_fee, fund.redemption_fee,
      fund.min_investment, fund.description
    ]);

    const fundId = result[0].insertId;

    // 插入基金标签关联
    for (const tagName of fund.tags) {
      const [tagResult] = await connection.execute('SELECT id FROM fund_tags WHERE name = ?', [tagName]);
      if (tagResult.length > 0) {
        await connection.execute(
          'INSERT IGNORE INTO fund_tag_relations (fund_id, tag_id) VALUES (?, ?)',
          [fundId, tagResult[0].id]
        );
      }
    }
    
    // 插入历史净值数据
    let nav = fund.nav;
    let date = new Date();
    date.setFullYear(date.getFullYear() - 1); // 从一年前开始

    for (let i = 0; i < 365; i++) {
        nav = nav * (1 + (Math.random() - 0.48) / 100); // 随机小幅波动
        const navDate = date.toISOString().slice(0, 10);
        await connection.execute(
            'INSERT IGNORE INTO fund_nav (fund_id, nav_date, nav, cumulative_nav) VALUES (?, ?, ?, ?)',
            [fundId, navDate, nav.toFixed(4), nav.toFixed(4)]
        );
        date.setDate(date.getDate() + 1);
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  setupCompleteDatabase().then(() => {
    process.exit(0);
  }).catch((error) => {
    console.error('设置失败:', error);
    process.exit(1);
  });
}

module.exports = { setupCompleteDatabase }; 